# -*- coding: utf-8 -*-
# @Time    : 2023/11/21 9:22
# @Author  : yan.wei
# @Email   : 13675196684@163.com
# @File    : CourseScheduleUtil.py
# @Software: PyCharm

# python = 3.11.6
# 第三方库版本说明：
# pyinstaller == 6.2.0
# pandas == 2.1.3
# PyQt5 == 5.15.10

# pyinstaller是通过UPX进行压缩操作，如果缺少upx，会导致exe变大
# 下载https://upx.github.io/
# 解压到pipenv虚拟环境对应的目录下，\项目名\Scripts
# 装好UPX之后，打包的exe比未安装UPX的exe小了

# 打包成exe
# pyinstaller -F -w xxx.py

# pyinstaller --onefile --windowed --icon=icon2.ico your_script.py

# pyinstaller --onefile --windowed --icon=icon2.ico --add-data "res;res" OA_CourseScheduleUtil_v2.1.py 

import sys
import os

def resource_path(relative_path):
    """ Get absolute path to resource, works for dev and for PyInstaller """
    try:
        # PyInstaller creates a temp folder and stores path in _MEIPASS
        base_path = sys._MEIPASS
    except Exception:
        base_path = os.path.abspath(".")

    return os.path.join(base_path, relative_path)

# 示例：读取多个 Excel 文件
excel_paths = [
    resource_path("res/中文班级课表模板.xlsx"),
    resource_path("res/Class timetable English template.xlsx"),
    resource_path("res/icon2.ico")
]
# 文件名列表

# 示例：读取多个 Excel 文件
for path in excel_paths:
    # 在这里加载每个 Excel 文件
    print(f"Loading Excel file from: {path}")

t_chi_path = excel_paths[0]
t_eng_path = excel_paths[1]
t_icon_path = excel_paths[2]


import os
import sys
import re
import subprocess
import pandas
from PyQt5.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QPushButton, QLabel, QFileDialog, \
    QTextBrowser, QSplitter, QDesktopWidget, QHBoxLayout, QLineEdit, QCheckBox, QRadioButton, QComboBox, QDateTimeEdit, QDateEdit, QTextEdit
from PyQt5.QtGui import QFont, QIcon
from PyQt5.QtCore import QDate
import openpyxl

class CourseScheduleApp(QMainWindow):
    def __init__(self):
        # super().__init__() 用于调用父类（或超类）的初始化方法的一种方式。在面向对象编程中，当子类继承自父类时，子类的实例化可能需要执行父类的初始化操作。
        super().__init__()  # 这行代码的作用是调用 QMainWindow 类的初始化方法，因为 CourseScheduleApp 类是 QMainWindow 类的子类。这样做的目的是确保在子类初始化时，父类的一些必要的初始化操作也能被执行。
        self.initUI()  # self.initUI() 是在 CourseScheduleApp 类的 __init__ 方法中调用的一个方法。在这个例子中，self.initUI() 的作用是在初始化 CourseScheduleApp 类的对象时立即调用 initUI 方法来初始化用户界面（UI）

    def initUI(self):
        # 添加标题
        self.setWindowTitle('课表自动生成工具v2.2')
        # self.setWindowIcon(QIcon(r'E:\P12_CourseScheduleUtil\res\icon2.ico'))  # 替换为实际图标文件的路径，用绝对路径，不然会不显示
        self.setWindowIcon(QIcon(t_icon_path)) # 替换为实际图标文件的路径，用绝对路径，不然会不显示
        self.setGeometry(100, 100, 400, 400)  # 调整窗口大小

        self.setStyleSheet(
            "QMainWindow { background-color: #f0f0f0; }"
            # "QPushButton { background-color: #009ACD; color: white; padding: 8px; font-size: 12px; border: none; }"
            # "QPushButton:hover { background-color: #2980b9; }"
            # "QRadioButton { color: #2c3e50; }"
            "QWidget { font-family: '微软雅黑'; }"
        )
        font = QFont()
        font.setPointSize(14)  # 设置字体大小为14
        self.setFont(font)

        # 获取屏幕的几何信息
        screen_geometry = QDesktopWidget().screenGeometry()
        self.centralWidget = QSplitter(self)  # 使用 QSplitter 创建可分割布局
        self.setCentralWidget(self.centralWidget)

        # 左右分布
        self.leftWidget = QWidget(self.centralWidget)  # 左侧工具
        # self.rightWidget = QWidget(self.centralWidget)  # 右侧工具

        self.centralWidget.addWidget(self.leftWidget)
        # self.centralWidget.addWidget(self.rightWidget)

        self.leftLayout = QVBoxLayout()
        # self.rightLayout = QVBoxLayout()

        # 计算窗口的位置使其居中
        windowWidth = self.frameGeometry().width()
        windowHeight = self.frameGeometry().height()
        x = (screen_geometry.width() - windowWidth) / 2
        y = (screen_geometry.height() - windowHeight) / 2
        self.move(int(x), int(y))

        # 设置左侧布局
        self.leftWidget.setLayout(self.leftLayout)
        # 编写左侧代码UI
        self.leftinfoLabel = QLabel('euni课表自动生成工具', self)
        self.leftinfoLabel.setStyleSheet('color: #0082ff; font-size: 18px; font-weight: bold;')
        self.leftLayout.addWidget(self.leftinfoLabel)

        self.text_label2 = QLabel('-------------------------------')
        self.text_label2.setStyleSheet('color: #383838; font-size: 12px; font-weight: bold;')
        self.leftLayout.addWidget(self.text_label2)

        self.horizontalLayoutLan = QHBoxLayout()
        self.text_label2 = QLabel('班级类型：\t')
        self.horizontalLayoutLan.addWidget(self.text_label2)
        self.leftLayout.addLayout(self.horizontalLayoutLan)
        self.chinaRadioButtonLeft = QRadioButton('国内', self)
        self.chinaRadioButtonLeft.setChecked(True)
        self.chinaRadioButtonLeft.toggled.connect(self.onChineseToggled)
        self.horizontalLayoutLan.addWidget(self.chinaRadioButtonLeft)
        #
        self.englishRadioButtonLeft = QRadioButton('国际', self)
        self.englishRadioButtonLeft.toggled.connect(self.onEnglishToggled)
        self.horizontalLayoutLan.addWidget(self.englishRadioButtonLeft)

        self.horizontalLayoutLan = QHBoxLayout()  # 创建一个新的水平布局
        self.text_label = QLabel('开始时间：\t')
        self.horizontalLayoutLan.addWidget(self.text_label)
        self.start_date = QDateEdit(self)
        self.start_date.setDisplayFormat("yyyy-MM-dd")
        self.start_date.setDate(QDate.currentDate())  # 设置默认值为当前日期
        self.start_date.setCalendarPopup(True)  # 显示弹出的日历
        self.horizontalLayoutLan.addWidget(self.start_date)
        self.leftLayout.addLayout(self.horizontalLayoutLan)  # 将水平布局添加到主布局中

        self.horizontalLayoutLan = QHBoxLayout()  # 创建一个新的水平布局
        self.text_label = QLabel('结束时间：\t')
        self.horizontalLayoutLan.addWidget(self.text_label)
        self.end_date = QDateEdit(self)
        self.end_date.setDisplayFormat("yyyy-MM-dd")
        self.end_date.setDate(QDate.currentDate())  # 设置默认值为当前日期
        self.end_date.setCalendarPopup(True)  # 显示弹出的日历
        self.horizontalLayoutLan.addWidget(self.end_date)
        self.leftLayout.addLayout(self.horizontalLayoutLan)  # 将水平布局添加到主布局中

        # 周末选项控件，星期
        self.horizontalLayout = QHBoxLayout()
        self.text_label1 = QLabel('是否包含周末：\t')
        # self.text_label1.setStyleSheet(' font-size: 18px; font-weight: bold;')
        self.horizontalLayout.addWidget(self.text_label1)
        self.leftLayout.addLayout(self.horizontalLayout)
        self.staCheckBox = QCheckBox('周六', self)
        self.horizontalLayout.addWidget(self.staCheckBox)
        self.sunCheckBox = QCheckBox('周日', self)
        self.horizontalLayout.addWidget(self.sunCheckBox)

        # 添加晚上是否上课控件，时间段
        self.horizontalLayoutNoon = QHBoxLayout()
        self.text_label1 = QLabel('是否包含晚上：\t')
        # self.text_label1.setStyleSheet('font-size: 18px; font-weight: bold;')
        self.horizontalLayoutNoon.addWidget(self.text_label1)
        self.leftLayout.addLayout(self.horizontalLayoutNoon)
        self.morningCheckBox = QCheckBox('上午', self)
        self.morningCheckBox.setChecked(True)
        self.horizontalLayoutNoon.addWidget(self.morningCheckBox)
        self.afternoonCheckBox = QCheckBox('下午', self)
        self.afternoonCheckBox.setChecked(True)
        self.horizontalLayoutNoon.addWidget(self.afternoonCheckBox)
        self.eveningCheckBox = QCheckBox('晚上', self)
        self.horizontalLayoutNoon.addWidget(self.eveningCheckBox)

        # 添加一个打开文件夹按钮，打开教材目录，自动生成课程内容列
        self.openFolderButton = QPushButton('打开教材存放目录', self)
        self.openFolderButton.clicked.connect(self.leftOpenFolder)
        self.leftLayout.addWidget(self.openFolderButton)

        # 添加teacher输入框
        self.teacher = QLineEdit(self)
        self.teacher.setPlaceholderText("培训讲师，格式：姓名+工号，例如：张三10888888")
        # self.teacher.setText('严威10191977')
        self.leftLayout.addWidget(self.teacher)

        # 添加上课形式
        self.comboBox_TeachingFormat = QComboBox(self)
        self.comboBox_TeachingFormat.addItem("授课")
        self.comboBox_TeachingFormat.addItem("实习")
        self.comboBox_TeachingFormat.addItem("考试")
        self.comboBox_TeachingFormat.addItem("Lecture")
        self.comboBox_TeachingFormat.addItem("Practice")
        self.comboBox_TeachingFormat.addItem("Exam")
        self.leftLayout.addWidget(self.comboBox_TeachingFormat)



        # 添加教室信息
        self.room = QLineEdit(self)
        # self.room.setText('001')
        self.room.setPlaceholderText("培训教室，例如：402，可选项，可不填写")
        self.leftLayout.addWidget(self.room)

        # 添加模块难度级别
        self.comboBox_TextbookLevel = QComboBox(self)
        self.comboBox_TextbookLevel.addItem("1级")
        self.comboBox_TextbookLevel.addItem("2级")
        self.comboBox_TextbookLevel.addItem("3级")
        self.comboBox_TextbookLevel.addItem("4级")
        self.comboBox_TextbookLevel.addItem("Level 1")
        self.comboBox_TextbookLevel.addItem("Level 2")
        self.comboBox_TextbookLevel.addItem("Level 3")
        self.comboBox_TextbookLevel.addItem("Level 4")
        self.leftLayout.addWidget(self.comboBox_TextbookLevel)

        # 培训讲师来源
        self.comboBox_TeacherSource = QComboBox(self)
        self.comboBox_TeacherSource.addItem("专职讲师(Full-time)")
        self.comboBox_TeacherSource.addItem("兼职讲师(Part-time)")
        self.comboBox_TeacherSource.addItem("外请讲师(Guest Lecturer)")
        self.comboBox_TeacherSource.addItem("国际外包(Outsourcing-International)")
        self.comboBox_TeacherSource.addItem("外包-北京协力(Outsourcing-Beijing Xieli)")
        self.comboBox_TeacherSource.addItem("外包-南京信雅达(Outsourcing-Nanjing Xinyada)")
        self.comboBox_TeacherSource.addItem("外包-南京华苏(Outsourcing-Nanjing Huasu)")
        self.leftLayout.addWidget(self.comboBox_TeacherSource)


        # 创建一个按钮用于打开文件夹选择对话框
        self.horizontalLayoutFolder = QHBoxLayout()  # 创建一个新的水平布局
        self.select_folder_button = QPushButton('选择课表保存路径', self)
        self.select_folder_button.clicked.connect(self.selectFolder)
        self.horizontalLayoutFolder.addWidget(self.select_folder_button)
        # 创建一个文本框用于显示路径
        self.path_display = QLineEdit(self)
        self.path_display.setReadOnly(True)
        self.horizontalLayoutFolder.addWidget(self.path_display)
        self.leftLayout.addLayout(self.horizontalLayoutFolder)


        # 添加一个启动按钮
        self.startButton = QPushButton('启动', self)
        self.startButton.clicked.connect(self.leftstartExtraction)
        # self.startButton.setFixedSize(400, 30)  # 设置宽度为120，高度为40
        self.leftLayout.addWidget(self.startButton)

        # ResultTextEdit
        self.leftResultTextEdit = QTextBrowser(self)
        self.leftResultTextEdit.setOpenExternalLinks(True)
        self.leftLayout.addWidget(self.leftResultTextEdit)


    def selectFolder(self):
        # 打开文件夹选择对话框
        selected_folder = QFileDialog.getExistingDirectory(self, "选择文件夹", "")

        # 如果用户选择了文件夹，更新文本框
        if selected_folder:
            self.path_display.setText(selected_folder)
    def onChineseToggled(self, checked):
        if checked:
            # 如果选择了“国内”，则将comboBox_TeachingFormat设置为"授课"
            index1 = self.comboBox_TeachingFormat.findText("授课")
            index2 = self.comboBox_TextbookLevel.findText("1级")
            if index1 >= 0:
                self.comboBox_TeachingFormat.setCurrentIndex(index1)
            if index2 >= 0:
                self.comboBox_TextbookLevel.setCurrentIndex(index2)
    def onEnglishToggled(self, checked):
        if checked:
            # 如果选择了“国际”，则将comboBox_TeachingFormat设置为"Lecture"
            index1 = self.comboBox_TeachingFormat.findText("Lecture")
            index2 = self.comboBox_TextbookLevel.findText("Level 1")
            if index1 >= 0:
                self.comboBox_TeachingFormat.setCurrentIndex(index1)
            if index2 >= 0:
                self.comboBox_TextbookLevel.setCurrentIndex(index2)
    def leftOpenFolder(self):
        folder_path = QFileDialog.getExistingDirectory(self, "选择文件夹", ".")
        if folder_path:
            self.folder_path = folder_path
            self.leftResultTextEdit.append(f'已选择的文件夹: {self.folder_path}')
        else:
            self.leftResultTextEdit.append('请选择教材存放目录！')
        # return folder_path

    def leftstartExtraction(self):
        # if not hasattr(self, 'leftxlsxFilePath'):
        #     self.leftResultTextEdit.append('请选择开班课表模板！')
        #     return
        try:
            if not self.text_label2:
                self.leftResultTextEdit.append('请选择班级类型！')
                return

            if not self.start_date.text():
                self.leftResultTextEdit.append('请选择开始时间！')
                return

            if not self.end_date.text():
                self.leftResultTextEdit.append('请选择结束时间！')
                return

            if not self.text_label1:
                self.leftResultTextEdit.append('请选择当天上课时间段！')
                return

            if not self.teacher.text():
                self.leftResultTextEdit.append('请输入培训讲师！')
                return

            if not self.comboBox_TeachingFormat.currentText():
                self.leftResultTextEdit.append('请选择授课形式！')
                return


            # if not self.room.text():
            #     self.leftResultTextEdit.append('请输入教室！')
            #     return

            if not self.folder_path:
                self.leftResultTextEdit.append('请选择教材存放目录！')
                return

            if not self.path_display.text():
                self.leftResultTextEdit.append('请选择课表保存路径！')
                return

            if self.chinaRadioButtonLeft.isChecked():
            # if hasattr(self, 'leftxlsxFilePath'):
                self.leftResultTextEdit.append('\n文件处理中...')
                self.weekendArr = []
                if self.staCheckBox.isChecked():
                    self.weekendArr.append(6)
                if self.sunCheckBox.isChecked():
                    self.weekendArr.append(7)

                self.noonArr = []
                if self.morningCheckBox.isChecked():
                    self.noonArr.append("上午")
                if self.afternoonCheckBox.isChecked():
                    self.noonArr.append("下午")
                if self.eveningCheckBox.isChecked():
                    self.noonArr.append("晚上")

                # self.t_file = 'res/中文班级课表模板.xlsx'
                self.t_file = t_chi_path
                folder_path_result = CourseScheduleUtilforEuni().fillTextChi(self.t_file,
                                                                      self.start_date.text(),
                                                                      self.end_date.text(),
                                                                      self.folder_path,
                                                                      self.teacher.text(),
                                                                      self.comboBox_TeachingFormat.currentText(),
                                                                      self.room.text(),
                                                                      self.comboBox_TextbookLevel.currentText(),
                                                                      self.comboBox_TeacherSource.currentText(),
                                                                      self.path_display.text(),
                                                                      self.weekendArr,
                                                                      self.noonArr)
                self.leftResultTextEdit.append(f'\n输出文件: {folder_path_result}')
                subprocess.Popen(['explorer', os.path.abspath(os.path.dirname(folder_path_result))],
                                 shell=True)  # Windows 上使用 "explorer"
                # self.leftResultTextEdit.append(f'\n输出文件: {self.path_display.text()}')
                # subprocess.Popen(['explorer', os.path.abspath(os.path.dirname(self.path_display.text()))],
                #                  shell=True)  # Windows 上使用 "explorer"

            # elif hasattr(self, 'leftxlsxFilePath') and self.englishRadioButtonLeft.isChecked():
            elif self.englishRadioButtonLeft.isChecked():
                self.leftResultTextEdit.append('\n文件处理中...')
                self.weekendArr = []
                if self.staCheckBox.isChecked():
                    self.weekendArr.append(6)
                if self.sunCheckBox.isChecked():
                    self.weekendArr.append(7)

                self.noonArr = []
                if self.morningCheckBox.isChecked():
                    self.noonArr.append("Morning")
                if self.afternoonCheckBox.isChecked():
                    self.noonArr.append("Afternoon")
                if self.eveningCheckBox.isChecked():
                    self.noonArr.append("Evening")
                # else:
                #     self.leftResultTextEdit.append("请选择上课时间！")
                # self.t_file = 'res/Class timetable English template.xlsx'
                self.t_file = t_eng_path
                folder_path_result = CourseScheduleUtilforEuni().fillTextEng(self.t_file,
                                                                      self.start_date.text(),
                                                                      self.end_date.text(),
                                                                      self.folder_path,
                                                                      self.teacher.text(),
                                                                      self.comboBox_TeachingFormat.currentText(),
                                                                      self.room.text(),
                                                                      self.comboBox_TextbookLevel.currentText(),
                                                                      self.comboBox_TeacherSource.currentText(),
                                                                      self.path_display.text(),
                                                                      self.weekendArr,
                                                                      self.noonArr)
                self.leftResultTextEdit.append(f'\n输出文件: {folder_path_result}')
                subprocess.Popen(['explorer', os.path.abspath(os.path.dirname(folder_path_result))],
                                 shell=True)  # Windows 上使用 "explorer"
            # else:
            #     self.leftResultTextEdit.append('\n请先选择一个模板文件。')

        except PermissionError as e:
            self.leftResultTextEdit.append('\n请先关闭生成文件！')
        except Exception as e:
            self.leftResultTextEdit.append(str(e))


class CourseScheduleUtilforEuni():
    def __init__(self):
        # 继承父类的方法
        super().__init__()

    def fillTextChi(self,
                    template_file,
                    start_date,
                    end_date,
                    folder_path,
                    teacher,
                    teaching_format,
                    room,
                    textbook_level,
                    teacher_source,
                    save_path,
                    weekendArr,
                    noonArr):
        # 初始化
        workbook = openpyxl.load_workbook(template_file)
        sheet = workbook['导入模板']

        # 日期
        date_range = self.generateDateRange(start_date, end_date, weekendArr, noonArr)
        for i, date in enumerate(date_range, start=3):
            sheet.cell(row=i, column=1, value=date.strftime('%Y-%m-%d'))

        # 星期
        # 填入星期数据
        weekday_map = {
            'Monday': '星期一',
            'Tuesday': '星期二',
            'Wednesday': '星期三',
            'Thursday': '星期四',
            'Friday': '星期五',
            'Saturday': '星期六',
            'Sunday': '星期日'
        }
        for i, date in enumerate(date_range, start=3):
            sheet.cell(row=i, column=2, value=weekday_map[date.day_name()])

        # 时间段
        # 填入上午下午数据
        periods = noonArr * (len(date_range) // len(noonArr))
        for i, period in enumerate(periods, start=3):
            sheet.cell(row=i, column=3, value=period)

        # 课时
        # 根据clumn = 3 的值，判断是上午还是下午，如果是上午或者下午，填入“4”，如果是晚上，填入“2”
        for i in range(3, 3 + len(date_range)):
            if sheet.cell(row=i, column=3).value == "上午" or sheet.cell(row=i, column=3).value == "下午":
                sheet.cell(row=i, column=6, value="4")
            elif sheet.cell(row=i, column=3).value == "晚上":
                sheet.cell(row=i, column=6, value="2")

        # 填入课程内容
        file_names = self.getFileNames(folder_path)
        # for i, name in file_names.items():
        #     sheet.cell(row=3 + i, column=4, value=name)

        # 以时间为基准，不足的文件名，用最后一个补齐
        if len(file_names) <= len(date_range):
            for i in range(3, 3 + len(date_range)):
                sheet.cell(row=i, column=4, value=file_names[min(i-3,len(file_names)-1)])
        # 如果文件名超出时间长度，将多余文件名拼接到最后一行
        if len(file_names) > len(date_range):
            for i in range(3, 3 + len(date_range) -1):
                sheet.cell(row=i, column=4, value=file_names[min(i-3,len(file_names)-1)])
            file_names_list = list(file_names.values())
            remaining_file_list = file_names_list[len(date_range)-1:]
            remaining_file = '\n'.join(remaining_file_list)
            sheet.cell(row=3 + len(date_range) -1 , column=4, value=remaining_file)

        # 以时间和文件名中最大的长度为基准，文件行数可能超出时间长度
        # for i in range(3, 3 + max(len(date_range),len(file_names))):
        #     sheet.cell(row=i, column=4, value=file_names[min(i - 3, len(file_names) - 1)])

        # 培训讲师姓名
        teacher_name = re.sub(r'\d+', '', teacher)
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=5, value=teacher_name)

        # G7列 形式
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=7, value=teaching_format)

        # H8列 教室
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=8, value=room)

        # I9模块代码
        file_names = self.getFileId(folder_path)
        # for i, name in file_names.items():
        #     sheet.cell(row=3 + i, column=9, value=name)
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=9, value=file_names[min(i - 3, len(file_names) - 1)])
        # 以时间和文件名中最大的长度为基准，文件行数可能超出时间长度
        # for i in range(3, 3 + max(len(date_range),len(file_names))):
        #     sheet.cell(row=i, column=9, value=file_names[min(i - 3, len(file_names) - 1)])

        # J10 模块难度
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=10, value=textbook_level)

        # K11 培训讲师工号
        teacher_id_1 = re.findall(r'\d+', teacher)
        # 将列表转换为字符串
        teacher_id = ''.join(teacher_id_1) if teacher_id_1 else ""
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=11, value=teacher_id)

        # L12 填入培训讲师来源
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=12, value=teacher_source)

        # 填入教师信息、课程类型、教室信息，从第七列开始
        # values = [teahing_format, room]
        # for idx, value in enumerate(values, start=7):
        #     for i in range(3, 3 + len(date_range)):
        #         sheet.cell(row=i, column=idx, value=value)

        # 将结果保存在origin_file_name文件所在的文件夹中，文件名为origin_file_name的文件名+‘done’
        # filename = os.path.splitext(os.path.basename(leftxlsxFilePath))[0]
        # output_filename = os.path.join(os.path.dirname(leftxlsxFilePath), filename + "_done.xlsx")
        # output_filename = save_path + '\\' + "中文班级课表_done.xlsx"
        output_filename = os.path.join(save_path, "中文班级课表_done.xlsx")
        workbook.save(output_filename)
        return output_filename

    def fillTextEng(self,
                    template_file,
                    start_date,
                    end_date,
                    folder_path,
                    teacher,
                    teaching_format,
                    room,
                    textbook_level,
                    teacher_source,
                    save_path,
                    weekendArr,
                    noonArr):
        # 初始化
        workbook = openpyxl.load_workbook(template_file)
        sheet = workbook['template']

        # 日期
        date_range = self.generateDateRange(start_date, end_date, weekendArr, noonArr)
        for i, date in enumerate(date_range, start=3):
            sheet.cell(row=i, column=1, value=date.strftime('%Y-%m-%d'))

        # 星期
        # 填入星期数据
        weekday_map = {
            'Monday': 'Mon',
            'Tuesday': 'Tue',
            'Wednesday': 'Wed',
            'Thursday': 'Thu',
            'Friday': 'Fri',
            'Saturday': 'Sat',
            'Sunday': 'Sun'
        }
        for i, date in enumerate(date_range, start=3):
            sheet.cell(row=i, column=2, value=weekday_map[date.day_name()])

        # 时间段
        # 填入上午下午数据
        periods = noonArr * (len(date_range) // len(noonArr))
        for i, period in enumerate(periods, start=3):
            sheet.cell(row=i, column=3, value=period)

        # 课时
        # 根据clumn = 3 的值，判断是上午还是下午，如果是上午或者下午，填入“4”，如果是晚上，填入“2”
        for i in range(3, 3 + len(date_range)):
            if sheet.cell(row=i, column=3).value == "Morning" or sheet.cell(row=i, column=3).value == "Afternoon":
                sheet.cell(row=i, column=6, value="4")
            elif sheet.cell(row=i, column=3).value == "Evening":
                sheet.cell(row=i, column=6, value="2")

        # 填入课程内容
        file_names = self.getFileNames(folder_path)
        # for i, name in file_names.items():
        #     sheet.cell(row=3 + i, column=4, value=name)

        # 以时间为基准，不足的文件名，用最后一个补齐
        if len(file_names) <= len(date_range):
            for i in range(3, 3 + len(date_range)):
                sheet.cell(row=i, column=4, value=file_names[min(i-3,len(file_names)-1)])
        # 如果文件名超出时间长度，将多余文件名拼接到最后一行
        if len(file_names) > len(date_range):
            for i in range(3, 3 + len(date_range) -1):
                sheet.cell(row=i, column=4, value=file_names[min(i-3,len(file_names)-1)])
            file_names_list = list(file_names.values())
            remaining_file_list = file_names_list[len(date_range)-1:]
            remaining_file = '\n'.join(remaining_file_list)
            sheet.cell(row=3 + len(date_range) -1 , column=4, value=remaining_file)

        # 以时间和文件名中最大的长度为基准，文件行数可能超出时间长度
        # for i in range(3, 3 + max(len(date_range),len(file_names))):
        #     sheet.cell(row=i, column=4, value=file_names[min(i - 3, len(file_names) - 1)])

        # 培训讲师姓名
        teacher_name = re.sub(r'\d+', '', teacher)
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=5, value=teacher_name)

        # G7列 形式
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=7, value=teaching_format)

        # H8列 教室
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=8, value=room)

        # I9模块代码
        file_names = self.getFileId(folder_path)
        # for i, name in file_names.items():
        #     sheet.cell(row=3 + i, column=9, value=name)
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=9, value=file_names[min(i - 3, len(file_names) - 1)])
        # 以时间和文件名中最大的长度为基准，文件行数可能超出时间长度
        # for i in range(3, 3 + max(len(date_range),len(file_names))):
        #     sheet.cell(row=i, column=9, value=file_names[min(i - 3, len(file_names) - 1)])

        # J10 模块难度
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=10, value=textbook_level)

        # K11 培训讲师工号
        teacher_id_1 = re.findall(r'\d+', teacher)
        # 将列表转换为字符串
        teacher_id = ''.join(teacher_id_1) if teacher_id_1 else ""
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=11, value=teacher_id)

        # L12 填入培训讲师来源
        for i in range(3, 3 + len(date_range)):
            sheet.cell(row=i, column=12, value=teacher_source)

        # 填入教师信息、课程类型、教室信息，从第七列开始
        # values = [teahing_format, room]
        # for idx, value in enumerate(values, start=7):
        #     for i in range(3, 3 + len(date_range)):
        #         sheet.cell(row=i, column=idx, value=value)

        # 将结果保存在origin_file_name文件所在的文件夹中，文件名为origin_file_name的文件名+‘done’
        # filename = os.path.splitext(os.path.basename(leftxlsxFilePath))[0]
        # output_filename = os.path.join(os.path.dirname(leftxlsxFilePath), filename + "_done.xlsx")
        # output_filename = save_path + '\\' + "中文班级课表_done.xlsx"
        output_filename = os.path.join(save_path, "英文班级课表_done.xlsx")
        workbook.save(output_filename)
        return output_filename

    def generateDateRange(self, start_date, end_date, weekendArr, noonArr):
        if len(weekendArr) == 0:
            return pandas.date_range(start_date, end_date, freq='B').repeat(len(noonArr))
        elif 6 in weekendArr and 7 in weekendArr:
            return pandas.date_range(start_date, end_date, freq='D').repeat(len(noonArr))
        elif 6 in weekendArr:
            date_range = pandas.date_range(start_date, end_date, freq='D').repeat(len(noonArr))
            return date_range[date_range.dayofweek != 6]
        else:
            date_range = pandas.date_range(start_date, end_date, freq='D').repeat(len(noonArr))
            return date_range[date_range.dayofweek != 5]

    # v2.1版本更新，支持遍历多层文件夹

    def getFileNames(sellf, folder_path):
        # 初始化一个空列表来存储所有符合要求的文件名
        Textbook_files = []

        # 使用 os.walk() 遍历文件夹及其子文件夹
        for root, dirs, files in os.walk(folder_path):
            for file in files:
                if file.endswith(('.pptx', '.docx')):
                    # 构建完整的文件路径
                    full_path = os.path.join(root, file)
                    # 添加到 Textbook_files 列表
                    Textbook_files.append(full_path)

        # 为每个文件提取用于排序的关键字
        def extract_sort_key(filename):
            parts = re.split(' |\.', os.path.basename(filename))
            # 假设数字位于第三个分隔的部分（索引为2）
            number_part = parts[0]
            try:
                # 尝试将该部分转换为整数
                return int(number_part)
            except ValueError:
                # 如果不能转换为整数，则使用原始字符串
                return number_part

        # 根据关键字排序文件列表
        Textbook_files.sort(key=extract_sort_key)

        file_names = {}
        for i, filename in enumerate(Textbook_files):
            name = re.split('_|-', os.path.basename(filename))[3:-1]
            name = re.split(' ', '_'.join(name))[1:]
            name = ' '.join(name)
            file_names[i] = name
        return file_names

    def getFileId(self, folder_path):
        # 初始化一个空列表来存储所有符合要求的文件名
        Textbook_files = []

        # 使用 os.walk() 遍历文件夹及其子文件夹
        for root, dirs, files in os.walk(folder_path):
            for file in files:
                if file.endswith(('.pptx', '.docx')):
                    # 构建完整的文件路径
                    full_path = os.path.join(root, file)
                    # 添加到 Textbook_files 列表
                    Textbook_files.append(full_path)

        # 为每个文件提取用于排序的关键字
        def extract_sort_key(filename):
            parts = re.split(' |\.', os.path.basename(filename))
            # 假设数字位于第三个分隔的部分（索引为2）
            number_part = parts[0]
            try:
                # 尝试将该部分转换为整数
                return int(number_part)
            except ValueError:
                # 如果不能转换为整数，则使用原始字符串
                return number_part

        # 根据关键字排序文件列表
        Textbook_files.sort(key=extract_sort_key)

        file_names = {}
        for i, filename in enumerate(Textbook_files):
            # name = re.split(' ', file)[1]
            name = re.split(' |\.', os.path.basename(filename))[1]
            name = re.split('_', name)[0:2]
            name = '_'.join(name)
            file_names[i] = name
        return file_names

        file_names = {}
        index = 0  # 记录文件索引
        # 递归遍历所有文件夹和文件
        for root, dirs, files in os.walk(Textbook_files):
            # 遍历文件列表
            for file in files:
                # 检查文件是否是 .pptx 或 .docx
                if file.endswith('.pptx') or file.endswith('.docx'):
                    # 提取文件名部分
                    name = re.split(' |\.', file)[1]
                    name = re.split('_', name)[0:2]
                    name = '_'.join(name)
                    file_names[index] = name
                    index += 1
        return file_names


if __name__ == '__main__':
    app = QApplication(sys.argv)
    mainWindow = CourseScheduleApp()
    mainWindow.show()
    sys.exit(app.exec_())


# pyinstaller --onefile --add-data "res;res" OA_CourseScheduleUtil_v2.1.py
